package core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.DbUtils;

import core.dao.SubcategoriaDao;
import core.model.Subcategoria;

/**
 * @author andoniibarguchilorenzo
 * clase que contiene los metodos implementados de la interface SubcategoriaDao
 */
public class SubcategoriaDaoImpl extends GenericDaoImpl<Subcategoria, Integer>
		implements SubcategoriaDao {

	@Override
	public List<Subcategoria> getAllSubcategorias() {
	

		Connection connection = DbUtils.createInstance();
		PreparedStatement ps = null;
		List<Subcategoria> subcategorias = new ArrayList<Subcategoria>();
		try {
			ps = connection
					.prepareStatement("SELECT COD_SUBCATEGORIA, NOMBRE, COD_CATEGORIA FROM SUBCATEGORIA");

			ResultSet rs = ps.executeQuery();
			Subcategoria subcategoria = null;

			while (rs.next()) {
				subcategoria = Subcategoria.createSubcategoria(rs.getInt(1),
						rs.getString(2), rs.getInt(3));
				subcategorias.add(subcategoria);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return subcategorias;
	}

	public void insertSubCategoria(Subcategoria subcategoria) {
		Connection connection = DbUtils.createInstance();
		PreparedStatement ps = null;
		try {
			// http://db.apache.org/derby/docs/10.7/ref/crefjavstateautogen.html
			ps = connection.prepareStatement(
					"INSERT INTO SUBCATEGORIA (NOMBRE,COD_CAT) VALUES (?,?)",
					PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, subcategoria.getNombre());
			ps.setInt(2, subcategoria.getCodigo_subcategoria());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	@Override
	public Subcategoria getSubcategoriaByCode(int cod_subcategoria) {
		
		Connection connection = DbUtils.createInstance();
		PreparedStatement ps = null;
		Subcategoria subcategoria = null;
		try {
			ps = connection
					.prepareStatement("SELECT COD_SUBCATEGORIA, NOMBRE, COD_CATEGORIA FROM SUBCATEGORIA WHERE COD_SUBCATEGORIA = ?");
			ps.setInt(1, cod_subcategoria);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				subcategoria = Subcategoria.createSubcategoria(rs.getInt(1),
						rs.getString(2), rs.getInt(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return subcategoria;
	}

	@Override
	public List<Subcategoria> getSubcategoriaByCodeCategoria(int cod_categoria) {
		
		Connection connection = DbUtils.createInstance();
		PreparedStatement ps = null;
		List<Subcategoria> subcategorias = new ArrayList<Subcategoria>();
		try {
			ps = connection
					.prepareStatement("SELECT COD_SUBCATEGORIA, NOMBRE, COD_CATEGORIA FROM SUBCATEGORIA WHERE COD_CATEGORIA = ?");
			ps.setInt(1, cod_categoria);

			ResultSet rs = ps.executeQuery();
			Subcategoria subcategoria = null;

			while (rs.next()) {
				subcategoria = Subcategoria.createSubcategoria(rs.getInt(1),
						rs.getString(2), rs.getInt(3));
				subcategorias.add(subcategoria);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return subcategorias;
	}

	@Override
	public Subcategoria getSubcategoriaByName(String nombre_subcategoria) {
	
		Connection connection = DbUtils.createInstance();
		PreparedStatement ps = null;
		Subcategoria subcategoria = null;
		try {
			ps = connection
					.prepareStatement("SELECT COD_SUBCATEGORIA, NOMBRE, COD_CATEGORIA FROM SUBCATEGORIA WHERE NOMBRE = ?");
			ps.setString(1, nombre_subcategoria);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				subcategoria = Subcategoria.createSubcategoria(rs.getInt(1),
						rs.getString(2), rs.getInt(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return subcategoria;
	}
}
